package org.jeecgframework.web.service.impl;

import com.alibaba.fastjson.JSON;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.jeecgframework.core.common.model.json.TreeBa;
import org.jeecgframework.core.common.model.json.ZTree;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.web.service.CbsCgreportHeadServiceI;
import org.jeecgframework.web.system.pojo.base.CbsCgreportHeadEntity;
import org.jeecgframework.web.system.pojo.base.CbsCustomizeReportEntity;
import org.jeecgframework.web.system.pojo.base.CbsRotaLevelEntity;
import org.jeecgframework.web.system.pojo.vo.SpecEntity;
import org.jeecgframework.web.system.service.SystemService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.io.Serializable;

@Service("cbsCgreportHeadService")
@Transactional
public class CbsCgreportHeadServiceImpl extends CommonServiceImpl implements CbsCgreportHeadServiceI {

    @Autowired
    SystemService systemService;

    public <T> void delete(T entity) {
        super.delete(entity);
        //执行删除操作配置的sql增强
        this.doDelSql((CbsCgreportHeadEntity) entity);
    }

    public <T> Serializable save(T entity) {
        Serializable t = super.save(entity);
        //执行新增操作配置的sql增强
        this.doAddSql((CbsCgreportHeadEntity) entity);
        return t;
    }

    public <T> void saveOrUpdate(T entity) {
        super.saveOrUpdate(entity);
        //执行更新操作配置的sql增强
        this.doUpdateSql((CbsCgreportHeadEntity) entity);
    }

    /**
     * 默认按钮-sql增强-新增操作
     *
     * @param
     * @return
     */
    public boolean doAddSql(CbsCgreportHeadEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-更新操作
     *
     * @param
     * @return
     */
    public boolean doUpdateSql(CbsCgreportHeadEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-删除操作
     *
     * @param
     * @return
     */
    public boolean doDelSql(CbsCgreportHeadEntity t) {
        return true;
    }

    /**
     * 替换sql中的变量
     *
     * @param sql
     * @return
     */
    public String replaceVal(String sql, CbsCgreportHeadEntity t) {
        sql = sql.replace("#{id}", String.valueOf(t.getId()));
        sql = sql.replace("#{cgr_sql}", String.valueOf(t.getCgrSql()));
        sql = sql.replace("#{property}", String.valueOf(t.getProperty()));
        sql = sql.replace("#{content}", String.valueOf(t.getContent()));
        sql = sql.replace("#{name}", String.valueOf(t.getName()));
        sql = sql.replace("#{create_date}", String.valueOf(t.getCreateDate()));
        sql = sql.replace("#{create_by}", String.valueOf(t.getCreateBy()));
        sql = sql.replace("#{UUID}", UUID.randomUUID().toString());
        return sql;
    }


    public List<CbsRotaLevelEntity> getRotalevels(String value) {
        return this.findByPropertyLike(CbsRotaLevelEntity.class, "level", value);
    }

    private ArrayList<SpecEntity> getJsonList(String jsonStr) {
        ArrayList<SpecEntity> specList = new ArrayList<SpecEntity>();
        net.sf.json.JSONObject spec_nameJSONObject = JSONObject.fromObject(jsonStr);
        Iterator<String> spec_nameIterator = spec_nameJSONObject.keys();
        while (spec_nameIterator.hasNext()) {
            String key = spec_nameIterator.next();
            SpecEntity apecEntity = new SpecEntity();
            apecEntity.setId(Integer.parseInt(key));
            apecEntity.setSpec(spec_nameJSONObject.getString(key));
            specList.add(apecEntity);
        }
        //看到没这个才是重点！！！！！！
        Collections.sort(specList);
        return specList;
    }

    interface ConstantInterface {
        /*与服务单有关系表*/
        String BJFW = " LEFT Join cbs_equipment_apply on cbs_equipment_apply.service_id=cbs_service.id ";//服务单和备件
        String SBFW = " LEFT JOIN zy_equipment_spare on zy_equipment_spare.sn = cbs_service.sn "; //服务单和设备
        String FHFW = " LEFT JOIN cbs_deliver on cbs_deliver.cr_code = cbs_service.`code` "; //服务单和发货主表
        String FJFW = " LEFT JOIN cbs_return_equipment on cbs_return_equipment.cr_code = cbs_service.`code` "; //返件和服务单
        String WXFW = " LEFT JOIN cbs_scene_report on cbs_scene_report.service_id = cbs_service.`id` ";//维修单和服务单

        /*与备件有关系的表*/
        String BJXQ = " LEFT JOIN cbs_equipment_apply_detail on cbs_equipment_apply.id = cbs_equipment_apply_detail.apply_id "; //备件和备件详情

        /*与发货相关的*/
        String FHXQ = " LEFT JOIN cbs_deliver_equipment on cbs_deliver.id = cbs_deliver_equipment.deliver_id "; //发货与发货详情
        String FHBj = " LEFT JOIN cbs_deliver on cbs_equipment_apply.id = cbs_deliver.equipment_apply_id ";

        /*返件与返件详情*/
        String FJXQ = " LEFT JOIN cbs_return_equipment_detail on cbs_return_equipment.`id` = cbs_return_equipment_detail.return_equipment_id ";
        String FJMX = " LEFT JOIN cbs_back_equipment_detail on cbs_return_equipment.`id` = cbs_back_equipment_detail.return_equipment_id AND cbs_back_equipment_detail.return_equipment_detail_id=cbs_return_equipment_detail.id ";//返件与返件详情
        String FJBJ = " LEFT JOIN cbs_return_equipment on cbs_equipment_apply.id = cbs_return_equipment.equipment_apply_id ";
        String FJFH = " LEFT JOIN cbs_return_equipment on cbs_deliver.`code` = cbs_return_equipment.deliver_code ";//发货与返件

    }

    @Override
    public Map getSqlResult(String vkorg, String otabId, String ttabName, String resultObj, String type) {
        String sql = "SELECT  ";
        String tjSql = " where ";
        StringBuffer fromSql = new StringBuffer();

        String[] mid = {};
        Set<Map<String,Object>> foreignKeyTabSet = new HashSet<>();
        if (StringUtil.isNotEmpty(vkorg)) {
            mid = vkorg.split(",");
            for (int i = 0; i < mid.length; i++) {
                String cbscrId = "SELECT CONCAT( CONCAT( rota_name, '.', numberic_field ), ' as ', CONCAT(\"'\", field_chinese, \"'\")) AS s,cbs_customize_report.* FROM cbs_customize_report WHERE id='" + mid[i] + "'";
//                String cbscrId = "SELECT CONCAT(CONCAT(IFNULL(foreignkey_tab,rota_name),'.',IFNULL(foreignkey_name,numberic_field)),' as ',CONCAT(\"'\", field_chinese, \"'\")) AS s, cbs_customize_report.* FROM cbs_customize_report WHERE id='" + mid[i] + "'";
                List<Map<String, Object>> map = findForJdbc(cbscrId);
                if (mid.length != (i + 1)) {
                    sql += map.get(0).get("s") + ",";
                } else {
                    sql += map.get(0).get("s");
                }
                foreignKeyTabSet.add(map.get(0));
            }
        } else {  //如果不点生成待选，直接执行时，执行 *
            sql += " * ";
        }

        if (StringUtil.isNotEmpty(otabId)) { //主表id
            CbsRotaLevelEntity cbsRotaLevel = get(CbsRotaLevelEntity.class, otabId.toString());
            if (!cbsRotaLevel.getRotaName().contains("/")) {
                fromSql.append(" FROM " + cbsRotaLevel.getRotaName() + " ");//表名
            } else {
                String s = cbsRotaLevel.getRotaName();
                if (s.contains("cbs_equipment_apply") || s.contains("cbs_equipment_apply_detail")) {
                    fromSql.append(" FROM cbs_equipment_apply " + ConstantInterface.BJXQ);//备件或者备件详情
                }
                if (s.contains("cbs_deliver") || s.contains("cbs_deliver_equipment")) {
                    fromSql.append(" FROM cbs_deliver " + ConstantInterface.FHXQ);  //发货或者发货详情
                }
                if (s.contains("cbs_return_equipment") || s.contains("cbs_return_equipment_detail")) {
                    fromSql.append(" FROM cbs_return_equipment " + ConstantInterface.FJXQ + ConstantInterface.FJMX);//返件或者返件详情
                }
            }

            if (StringUtil.isNotEmpty(ttabName)) {  /*关联表*/
                JSONArray jsonArr = JSONArray.fromObject(ttabName);
                for (Object o : jsonArr) {
                    String tabName = o.toString().replace(",", "");
                    for (String s : tabName.split("/")) {
                        if (s.equals("cbs_equipment_apply")) { //备件
                            if (cbsRotaLevel.getRotaName().contains("cbs_service")) { //如果主表是服务单
                                fromSql.append(ConstantInterface.BJFW);
                            }
                        } else if (s.equals("cbs_deliver")) { //发货
                            if (cbsRotaLevel.getRotaName().contains("cbs_service")) { //如果主表是服务单
                                fromSql.append(ConstantInterface.FHFW);
                                if(fromSql.indexOf("cbs_return_equipment") != -1){
                                    fromSql.append("  AND cbs_return_equipment.deliver_code = cbs_deliver.`code` ");
                                }
                            }
                            if (cbsRotaLevel.getRotaName().contains("cbs_equipment_apply")) { //如果主表是备件
                                fromSql.append(ConstantInterface.FHBj);
                            }
                        } else if (s.equals("cbs_scene_report")) { //完工报告
                            if (cbsRotaLevel.getRotaName().contains("cbs_service")) { //如果主表是服务单
                                fromSql.append(ConstantInterface.WXFW);
                            }
                        } else if (s.equals("cbs_return_equipment")) {  //返件
                            if (cbsRotaLevel.getRotaName().contains("cbs_service")) { //如果主表是服务单
                                fromSql.append(ConstantInterface.FJFW);
                                if (fromSql.indexOf("cbs_deliver") != -1){
                                    fromSql.append("  AND cbs_return_equipment.deliver_code = cbs_deliver.`code` ");
                                }
                            }
                            if (cbsRotaLevel.getRotaName().contains("cbs_equipment_apply")) { //如果主表是备件
                                fromSql.append(ConstantInterface.FJBJ);
                            }
                            if (cbsRotaLevel.getRotaName().contains("cbs_deliver")) { //如果主表是发货
                                fromSql.append(ConstantInterface.FJFH);
                            }
                        } else if (s.equals("zy_equipment_spare")) { //设备
                            if (cbsRotaLevel.getRotaName().contains("cbs_service")) { //如果主表是服务单
                                fromSql.append(ConstantInterface.SBFW);
                            }
                        } else {
                            if (s.equals("cbs_equipment_apply_detail")) {
                                //详情表关联
                                fromSql.append(ConstantInterface.BJXQ);
                            }
                            if (s.equals("cbs_deliver_equipment")) {
                                //详情表关联
                                fromSql.append(ConstantInterface.FHXQ);
                                if (fromSql.indexOf("cbs_return_equipment_detail") != -1){
                                    fromSql.append(" AND cbs_deliver_equipment.id=cbs_return_equipment_detail.deliver_equuipment_id ");
                                    fromSql.append(ConstantInterface.FJMX);
                                }
                            }
                            if (s.equals("cbs_return_equipment_detail")) {
                                //详情表关联
                                fromSql.append(ConstantInterface.FJXQ);
                                if (fromSql.indexOf("cbs_deliver_equipment") != -1){
                                    fromSql.append(" AND cbs_deliver_equipment.id=cbs_return_equipment_detail.deliver_equuipment_id ");
	                                fromSql.append(ConstantInterface.FJMX);
                                }
                            }
                        }
                    }
                }
            }
            Map<String,StringBuffer> foreignKeyMap = new HashMap<>();
            if (foreignKeyTabSet.size()>0){
                for (Map<String,Object> map: foreignKeyTabSet){
                    if (StringUtil.isNotEmpty(map.get("foreignkey_tab"))){
                        StringBuffer foreignKeySql = new StringBuffer();
	                    //以cbs_customize_report的foreignkey_tab , numeric_field 为关联表的别名防止出现重复关联同一张表没有别名导致报错（若仍会出现重复则再将别名对应上rota_name）
                        String foreignKeyTabName = map.get("foreignkey_tab")+"_"+map.get("numberic_field");
                        foreignKeySql.append(" LEFT JOIN "+map.get("foreignkey_tab")+" "+foreignKeyTabName+" ON "+foreignKeyTabName+"."+map.get("foreignkey")+"="+map.get("rota_name")+"."+map.get("numberic_field")+" ");
                        if (!foreignKeyMap.containsKey(foreignKeyTabName)){
                            foreignKeyMap.put(foreignKeyTabName,foreignKeySql);
                        }
                    }
                }
            }
            for (String key:foreignKeyMap.keySet()){
                fromSql.append(foreignKeyMap.get(key));
            }
        }

        net.sf.json.JSONArray Jsonarray = new JSONArray();  //存储json格式的字符
        //查询条件拼接
        List listArr = new ArrayList();
        if (StringUtil.isNotEmpty(resultObj) && !resultObj.equals("false")) {
            JSONObject jsonObject = JSONObject.fromObject(resultObj);
            int size = jsonObject.size();
            Set set = new HashSet();

            /*拼接json*/
            ArrayList<SpecEntity> list = getJsonList(jsonObject.toString());
            for (SpecEntity s : list) {
                JSONArray array = JSONArray.fromObject(s.getSpec());
                net.sf.json.JSONObject Json = new net.sf.json.JSONObject();
                String cun = array.get(0).toString();
                String[] arrStr = cun.split("\\.");
                Json.put("tabname", arrStr[0]);//表名
                String sqlArr = "SELECT id,field_chinese from cbs_customize_report rep WHERE rep.numberic_field = '" + arrStr[1] + "' and rep.rota_name = '" + arrStr[0] + "'";
                List<Map<String, Object>> map = systemService.findForJdbc(sqlArr);
                for (Map<String, Object> stringObjectMap : map) {
                    Json.put("id", stringObjectMap.get("id"));//表id
                    Json.put("field_chinese", stringObjectMap.get("field_chinese"));//表字段
                }
                Json.put("querycriteria", array.get(1).toString());//查询条件
                Json.put("content", array.get(2).toString());//查询内容
                Json.put("isgroup", array.get(3).toString());//是否分组
                Json.put("condition", array.get(4).toString());//组合条件
                Json.put("timequery", array.get(5).toString());//是否时间差查询

                Jsonarray.add(Json);
            }
            /*json组合 end*/

            Iterator<String> its1 = jsonObject.keys();
            while (its1.hasNext()) {
                String key = its1.next(); //key
                String value = jsonObject.getString(key);  //value
                JSONArray array = JSONArray.fromObject(value);
                if (array.get(3).equals("0")) {  /*是否查询条件 1：是 0：否*/
                    its1.remove();
                }
            }

            for (SpecEntity s : list) {
                JSONArray array = JSONArray.fromObject(s.getSpec());
                String marrsy[] = new String[7];
                //时间差查询
                if (array.get(5).equals("1")) {  //时间差查询
                    String values = jsonObject.getString(String.valueOf(s.getId() + 1));
                    JSONArray arrays = JSONArray.fromObject(values);
                    String beginTime = array.get(0).toString();
                    marrsy[0] = beginTime; //开始时间
                    marrsy[1] = arrays.get(0).toString(); //结束时间

                    //查询条件 转换
                    String czsql1 = "select include_symbol as includes from cbs_condition where id='" + arrays.get(1).toString() + "'";
                    List<Map<String, Object>> czmap1 = systemService.findForJdbc(czsql1);
                    marrsy[2] = czmap1.get(0).get("includes").toString();

                    marrsy[3] = arrays.get(2).toString(); //查询的时间内容
                    marrsy[4] = arrays.get(3).toString(); //是否分组
                    marrsy[5] = array.get(4).toString(); //与下一个条件是and或者or 的关系
                    listArr.add(marrsy);
                    set.add(s.getId());
                    set.add(String.valueOf(s.getId() + 1));
                }
            }

            //移除元素
            for (Object o : set) {
                jsonObject.remove(String.valueOf(o));
            }

            //不包含时间差，以及分组的行条件
            List<JSONArray> joList = new ArrayList<>();
            Set<String> joKeySet = jsonObject.keySet();
            for (int i = 0;i<size;i++){
                for (String j: joKeySet){
                    if (Integer.parseInt(j) == i){
                        joList.add(JSONArray.fromObject(jsonObject.get(j)));
                        break;
                    }
                }
                if (joList.size() == joKeySet.size()){
                    break;
                }
            }
            for (JSONArray array : joList) {
                String cz = "";
                if (array.size() > 0) {
                    String czsql = "select include_symbol as includes from cbs_condition where condition_parent_id is  not NULL and id='" + array.get(1) + "'";
                    List<Map<String, Object>> czmap = systemService.findForJdbc(czsql);
                    if(czmap.size()>0){
                        cz = czmap.get(0).get("includes").toString();

                        for (int i = 0; i < array.size(); i++) {
                            if (i == 0) {
                                for (Map<String,Object> map : foreignKeyTabSet){
                                    String tj = map.get("rota_name") + "." + map.get("numberic_field");
                                    if (tj.equalsIgnoreCase(array.get(i).toString())){
                                        if (StringUtil.isNotEmpty(map.get("foreignkey_tab"))){
                                            String foreignKeyTj = map.get("foreignkey_tab")+"_"+map.get("numberic_field")+ "." + map.get("foreignkey_name");
                                            tjSql += foreignKeyTj + " ";
                                            break;
                                        }else{
                                            tjSql += array.get(i) + " ";
                                            break;
                                        }
                                    }
                                }

                            }
                            if (i == 1) {
                                tjSql += cz + " ";
                            }
                            if (i == 2) {
                                //not in / in 用（） ,like 用%%匹配 , 不为空，或者为空时，不查询后面的内容
                                if (cz.equals("not in") || cz.equals("in")) {
                                	// modify start 181107 Amy 修改为逗号拼接多个
                                	if(array.get(i) != null || array.get(i).toString().length() > 0) {
                                		List<String> result = Arrays.asList(array.get(i).toString().split(","));
                                		tjSql += "('" + result.get(0) + "'";
                                		 for (int j = 1; j < result.size(); j++) {
                                			 tjSql += ",'" + result.get(j) + "'";
                                		 }
                                		 tjSql += ") ";
                                	}
                                    //tjSql += "('" + array.get(i) + "')" + " ";
                                	// modify end 181107
                                } else if (cz.equals("like")) {
                                    tjSql += "'%" + array.get(i) + "%'" + " ";
                                } else if (cz.equals("is null")) {
                                    tjSql += "";
                                } else if (cz.equals("is not null")){
                                    tjSql += " AND "+array.get(0)+"!='' ";
                                } else {
                                    tjSql += "'" + array.get(i) + "'" + " ";
                                }
                            }
                            if(i == 4){
                                tjSql += array.get(i) + " ";
                            }
                        }
                    }
                }
            }
        }

        //拼接时间差查询sql
        for (int i = 0; i < listArr.size(); i++) {
            String marrsy[] = (String[]) listArr.get(i);
            tjSql += " TIME_TO_SEC(" + marrsy[0].toString() + ") - TIME_TO_SEC(" + marrsy[1].toString()+") " + marrsy[2].toString() + " " + marrsy[3].toString() + " * 60 * 60 " + marrsy[5].toString();
        }

        //自定义报表查询条件最后一个组合条件无效处理
        tjSql = tjSql.toUpperCase().trim();
        if(tjSql.endsWith("AND")){
        	tjSql = tjSql.substring(0,tjSql.length()-3);
        }
        if(tjSql.endsWith("OR")){
        	tjSql = tjSql.substring(0,tjSql.length()-2);
        }
        if (tjSql.endsWith("WHERE")){
            tjSql = tjSql.substring(0,tjSql.length()-5);
        }

    	// add start 181203 Amy 处理OR语句加括号
        String[] strArr = tjSql.split(" OR ");
        int ior =strArr.length -1;//OR出现的次数
        for (int i = 1; i <= ior; i++) {
        	int index = StringUtils.ordinalIndexOf(tjSql," OR ",i);
        	StringBuffer s1 = new StringBuffer(tjSql);
    		if (s1.lastIndexOf(" AND ", index) > 0 ) {
    			s1.insert(s1.lastIndexOf(" AND ", index)+5, "(");
    		} else {
    			s1.insert(s1.lastIndexOf(" WHERE ", index)+7, "(");
    		}
    		if (s1.indexOf(" AND ", index) > 0 ) {
    			s1.insert(s1.indexOf(" AND ", index), ")");
    		} else {
    			s1.append( ")");
    		}
    		tjSql = s1.toString();
        }
       	// add end 181203 
        
        String sqlList = sql + fromSql.toString() + tjSql;

        //预览只查询前20条
        if (type.equals("1")) {
            sqlList += " limit 0,20 ";
        }

        Map map = new HashMap();
        map.put("json", Jsonarray);  //拼接的json
        map.put("sql", sqlList.toString()); //拼接的sql
        return map;
    }


    @Override
    public String getzdNameForTableName(String name) {
        List<TreeBa> trees = new ArrayList<>();
        String[] split = name.split(",");
        for (int i = 0; i < split.length; i++) {
            if (!split[i].equals("-- 请选择 --")) {
                List<TreeBa> treeBas = recursiveTree(split[i]);
                trees.addAll(treeBas);
            }
        }
        String s = JSON.toJSONString(trees);
        System.out.println(s);
        return s;
    }

    public List<TreeBa> recursiveTree(String name) {
        List<TreeBa> list = new ArrayList<>();
        TreeBa tree = new TreeBa();
        tree.setId(String.valueOf(getUUID()));
        if (name.equals("cbs_service")) {
            tree.setName("服务单表");
        } else if (name.equals("cbs_deliver")) {
            tree.setName("发货单表");
        } else if (name.equals("cbs_equipment_apply")) {
            tree.setName("备件申请表");
        } else if (name.equals("cbs_scene_report")) {
            tree.setName("维修完工单表");
        } else if (name.equals("cbs_return_equipment")) {
            tree.setName("返件单表");
        } else if (name.equals("cbs_return_equipment_detail")) {
            tree.setName("返件详情表");
        } else if (name.equals("cbs_back_equipment_detail")) {
            tree.setName("返件入库表");
        } else if (name.equals("cbs_deliver_equipment")) {
            tree.setName("发货备件信息表");
        } else if (name.equals("cbs_equipment_apply_detail")) {
            tree.setName("备件申请信息表");
        } else if (name.equals("zy_equipment_spare")) {
            tree.setName("设备表");
        }
        List<CbsCustomizeReportEntity> cbsCustomizeReportEntityList = systemService.findHql("from CbsCustomizeReportEntity where rotaName = '" + name + "'");
        for (CbsCustomizeReportEntity cbsCustomizeReportEntity : cbsCustomizeReportEntityList) {
            ZTree tree1 = new ZTree();
            tree1.setId(String.valueOf(cbsCustomizeReportEntity.getId()));
            tree1.setName(cbsCustomizeReportEntity.getFieldChinese());
            tree.getChildren().add(tree1);

        }
        list.add(tree);
        return list;
    }

    //得到32位的uuid
    public static int getUUID() {
        System.out.println((int) ((Math.random() * 9 + 1) * 1000));

        return (int) ((Math.random() * 9 + 1) * 1000);

    }
}